package com.llsydn.data.sqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Toast;
import com.llsydn.hello.R;

/**
 * @author lilinshen
 * @title SQLite数据库存储
 * @description 请填写相关描述
 * @date 2018/9/26 10:18
 */
public class SqliteActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
    }

    /**
     * 创建数据库
     *
     * @param view
     */
    public void testCreateDB(View view) {
        DBHelper dbHelper = new DBHelper(this, 1);
        //获取连接
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        Toast.makeText(this, "创建数据库", Toast.LENGTH_SHORT).show();
    }

    /**
     * 更新库
     *
     * @param view
     */
    public void testUpdateDB(View view) {
        DBHelper dbHelper = new DBHelper(this, 2);
        //获取连接
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        Toast.makeText(this, "更新数据库", Toast.LENGTH_SHORT).show();
    }

    /**
     * 添加纪录
     *
     * @param view
     */
    public void testInsert(View view) {
        //1.得到连接
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //2.执行insert
        //insert into person(name,age) values('llsydn', 48)
        ContentValues values = new ContentValues();
        values.put("name", "llsydn");
        values.put("age", 48);
        Long id = database.insert("person", null, values);
        //3.关闭连接
        database.close();
        //4.提示
        Toast.makeText(this, "数据添加成功，id=" + id, Toast.LENGTH_SHORT).show();
    }

    /**
     * 更新记录
     *
     * @param view
     */
    public void testUpdate(View view) {
        //1.得到连接
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //2.执行update
        //update person set name=lack, age=13 where _id=3
        ContentValues values = new ContentValues();
        values.put("name", "lack");
        values.put("age", 13);
        int updateCount = database.update("person", values, "_id=?", new String[]{"3"});
        //3.关闭连接
        database.close();
        //4.提示
        Toast.makeText(this, "数据已更新" + updateCount + "条", Toast.LENGTH_SHORT).show();
    }

    /**
     * 删除记录
     *
     * @param view
     */
    public void testDelete(View view) {
        //1.得到连接
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //2.执行delete
        //delete from person where _id=3
        int deleteCount = database.delete("person", "_id=3", null);
        //3.关闭连接
        database.close();
        //4.提示
        Toast.makeText(this, "数据已删除" + deleteCount + "条", Toast.LENGTH_SHORT).show();
    }

    /**
     * 查询记录
     *
     * @param view
     */
    public void testQuery(View view) {
        //1.得到连接
        DBHelper dbHelper = new DBHelper(this, 2);
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        //2.执行query
        //select * from person where _id=3,
        Cursor cursor = database.query("person", null,
                "_id=?", new String[]{"1"}, null, null, null);
        //取出cursor中所有的数据
        while (cursor.moveToNext()) {
            int id = cursor.getInt(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            Log.e("TAG", id + "-" + name + "-" + age);
        }
        int count = cursor.getCount();
        //3.关闭连接
        database.close();
        //4.提示
        Toast.makeText(this, "查询到数据" + count + "条", Toast.LENGTH_SHORT).show();
    }

    /**
     * 事务测试
     * update person set age=23 where _id=1
     * update person set age=25 where _id=2
     * 一个功能中对数据库进行的多个操作：要么都成功，要么都失败
     * 事务处理的步骤：
     * 1.开始事务（在获取连接后）
     * 2.设置事务成功（在全部正常执行完后）
     * 3.结束事务（无论成功或失败，finally中）
     *
     * @param view
     */
    public void testTransaction(View view) {
        SQLiteDatabase database = null;
        try {
            DBHelper dbHelper = new DBHelper(this, 2);
            database = dbHelper.getReadableDatabase();
            //1.开启事务
            database.beginTransaction();

            //2.执行update
            //update person set age=23 where _id=1
            ContentValues values = new ContentValues();
            values.put("age", 23);
            int updateCount1 = database.update("person", values, "_id=?", new String[]{"1"});

            //中间出了异常
            boolean flag = true;
            if (flag) {
                throw new RuntimeException("出异常啦！");
            }

            //update person set age=25 where _id=2
            values = new ContentValues();
            values.put("age", 25);
            int updateCount2 = database.update("person", values, "_id=?", new String[]{"2"});

            //设置事务成功（在全部正常执行完后）
            database.setTransactionSuccessful();

        } catch (Exception e) {
            e.printStackTrace();
            Toast.makeText(this, "出异常了", Toast.LENGTH_SHORT).show();
        } finally {
            // 3.结束事务；关闭连接
            if (null != database) {
                database.endTransaction();
                database.close();
            }
        }
        //4.提示
        Toast.makeText(this, "事务测试结束", Toast.LENGTH_SHORT).show();
    }

}
